❮

privileges for ALTER ROLE/DATABASE SET

  • Jump to comment-1
    nathandbossart@gmail.com2022-07-22T20:04:22+00:00
    Hi hackers, Presently, if a role has privileges to SET a parameter, it is able to ALTER ROLE/DATABASE SET that parameter, provided it otherwise has permission to alter that role/database. This includes cases where the role only has SET privileges via the new pg_parameter_acl catalog. For example, if a role is granted the ability to SET a PGC_SUSET GUC, it also has the ability to ALTER ROLE/DATABASE SET that GUC. A couple of recent threads have alluded to the possibility of introducing a new set of privileges for ALTER ROLE/DATABASE SET [0] [1], so I thought I'd start the discussion. First, is it necessary to introduce new privileges, or should the ability to SET a parameter be enough to ALTER ROLE/DATABASE SET it? AFAICT this is roughly the behavior before v15, but it simply disallowed non-superusers from setting certain parameters. Second, if new privileges are required, what would they look like? My first instinct is to add GRANT ALTER ROLE ON PARAMETER and GRANT ALTER DATABASE ON PARAMETER. Thoughts? [0] https://postgr.es/m/1732511.1658332210%40sss.pgh.pa.us [1] https://postgr.es/m/20220714225735.GB3173833%40nathanxps13 -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
    • Jump to comment-1
      tgl@sss.pgh.pa.us2022-07-22T20:16:14+00:00
      Nathan Bossart <nathandbossart@gmail.com> writes: > Presently, if a role has privileges to SET a parameter, it is able to ALTER > ROLE/DATABASE SET that parameter, provided it otherwise has permission to > alter that role/database. This includes cases where the role only has SET > privileges via the new pg_parameter_acl catalog. For example, if a role is > granted the ability to SET a PGC_SUSET GUC, it also has the ability to > ALTER ROLE/DATABASE SET that GUC. A couple of recent threads have alluded > to the possibility of introducing a new set of privileges for ALTER > ROLE/DATABASE SET [0] [1], so I thought I'd start the discussion. > First, is it necessary to introduce new privileges, or should the ability > to SET a parameter be enough to ALTER ROLE/DATABASE SET it? Clearly, you need enough privilege to SET the parameter, and you need some sort of management privilege on the target role or DB. There might be room to discuss what that per-role/DB privilege needs to be. But I'm very skeptical that we need to manage this at the level of the cross product of GUCs and roles/DBs, which is what you seem to be proposing. That seems awfully unwieldy, and is there really any use-case for it? regards, tom lane
      • Jump to comment-1
        nathandbossart@gmail.com2022-07-22T22:25:16+00:00
        On Fri, Jul 22, 2022 at 04:16:14PM -0400, Tom Lane wrote: > Clearly, you need enough privilege to SET the parameter, and you need > some sort of management privilege on the target role or DB. There > might be room to discuss what that per-role/DB privilege needs to be. > But I'm very skeptical that we need to manage this at the level > of the cross product of GUCs and roles/DBs, which is what you seem > to be proposing. That seems awfully unwieldy, and is there really > any use-case for it? Actually, I think my vote is to do nothing, except for perhaps updating the documentation to indicate that SET privileges on a parameter are sufficient for ALTER ROLE/DATABASE SET (given you have the other required privileges for altering the role/database). I can't think of a use-case for allowing a role to SET a GUC but not change the session default for another role. And I agree that requiring extra permissions for this feels excessive. Maybe someone else has a use-case in mind, though. I figured it would be good to hash this out prior to 15.0, at which point changing the behavior would become substantially more difficult. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com